SQL优化,百万级2张表关联,从40分钟到3秒的历程 | 您所在的位置:网站首页 › sql server 分页优化 › SQL优化,百万级2张表关联,从40分钟到3秒的历程 |
表结构如下:
CREATE TABLE `deviceback` ( `id` int(11) NOT NULL AUTO_INCREMENT, `imei` varchar(100) NOT NULL COMMENT '手机唯一标识', `mid` varchar(50) DEFAULT NULL, `mac` varchar(100) DEFAULT NULL, `APNType` varchar(100) DEFAULT NULL, `status` int(11) DEFAULT '0', `ip` varchar(100) DEFAULT NULL, `sn` varchar(100) DEFAULT NULL COMMENT '系列号', `oem` varchar(100) DEFAULT NULL COMMENT '厂商', `product` varchar(100) DEFAULT NULL COMMENT '产品', `region` varchar(100) DEFAULT NULL COMMENT '区域', `operator` varchar(100) DEFAULT NULL COMMENT '运营商', `sim` varchar(100) DEFAULT NULL COMMENT 'sim卡号', `push_time` timestamp NULL DEFAULT NULL COMMENT '第一次登陆时间', `origin_version` varchar(100) DEFAULT NULL COMMENT '原始版本', `province` varchar(100) DEFAULT NULL COMMENT '省份', `provinceCode` varchar(100) DEFAULT NULL COMMENT '省份code', `city` varchar(50) DEFAULT NULL COMMENT '城市', `cityCode` varchar(50) DEFAULT NULL COMMENT '城市code', `brands` varchar(50) DEFAULT '0', `version` varchar(100) DEFAULT '0' COMMENT '客户端版本号', `last_checktime` timestamp NULL DEFAULT NULL COMMENT '最后一次登录时间', PRIMARY KEY (`id`), FULLTEXT KEY `NewIndex1` (`imei`), FULLTEXT KEY `NewIndex2` (`mid`), FULLTEXT KEY `NewIndex3` (`product`), FULLTEXT KEY `NewIndex4` (`brands`) ) ENGINE=MyISAM AUTO_INCREMENT=6832460 DEFAULT CHARSET=utf8;
CREATE TABLE `20130602_AppLog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `imei` varchar(100) DEFAULT NULL, `mid` varchar(50) NOT NULL DEFAULT '', `status` char(1) NOT NULL DEFAULT '0', `mac` varchar(100) DEFAULT 'NULL', `sn` varchar(100) DEFAULT NULL, `sim` varchar(100) DEFAULT NULL, `coperator` varchar(100) DEFAULT NULL, `version` varchar(100) DEFAULT NULL, `logintime` datetime DEFAULT NULL, `ip` varchar(20) DEFAULT NULL, `origin_version` varchar(100) DEFAULT NULL, `now_version` varchar(100) DEFAULT NULL, `APNType` varchar(20) DEFAULT NULL, `oem` varchar(20) DEFAULT NULL, `product` varchar(100) DEFAULT NULL, `region` varchar(100) DEFAULT NULL, `operator` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `NewIndex1` (`imei`), FULLTEXT KEY `NewIndex2` (`mid`) ) ENGINE=MyISAM AUTO_INCREMENT=3123866 DEFAULT CHARSET=utf8;
SQL如下: SELECT * FROM deviceback d,20130602_AppLog g WHERE d.mid=g.mid AND d.imei=g.imei AND d.mac=g.mac AND d.brands=0 AND g.coperator '' limit 20; explain 结果: 从这里可以看到ALL以及key为NULL,就是全表扫描,没有走索引,索引失效了!
1 然后我建议建上加上联合索引,试试看效果如何:
2 效果还是比较慢的,看来得换种办法了,检查所有关联字段,将为null的数据改成''。还是没有效果。
3 不查*了,直接count(*) 看看结果集大小,结果还是卡住了,短时间内没有出查询结果。
4,到这里我猜测估计是数据的构成问题,那就一个个条件去掉去尝试了。 SELECT * FROM deviceback d,20130602_AppLog g WHERE d.mid=g.mid AND d.imei=g.imei AND d.mac=g.mac AND d.brands=0 limit 20; 去掉 条件试试看,God,结果是37秒就出来了,好,大概问题找到了,出在这条判断语句里面,也就是 AND g.coperator '' 这个影响还蛮大的。 那就先在20130602_AppLog 表的coperator字段单独建索引试试看,然后查下 20130602_AppLog里面 g.coperator ''的有多少? 结果是比较令人欣慰的,单独查询,一秒不到出来结果了,6W多条纪录。 SELECT COUNT(1) FROM 20130602_AppLog gg WHERE gg.coperator '' 63987 然后再试试整个sql,看需要多长时间。 先看下explain结果:OK,27秒出来了,AND d.mid=g.mid这个之前在第一条,现在放在后面,原因是手机唯一标示这个字段有空值。 God,最讨厌关键业务字段null值了,给我们的优化工作带来巨大的烦恼。
切记:大家以后千万要记住关键业务字段不能允许录入null值。 |
今日新闻 |
推荐新闻 |
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 |